package iedcyt.acute.dataAccess.dao.maauco;

import iedcyt.acute.dataAccess.domain.maauco.Ma0;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.maauco.IMa0Dao;

import java.util.List;



public class Ma0Dao extends SpringDao implements IMa0Dao  {

	String cols = "IDNO, NUMAUTORES, NUMORG, TC, RF, TITULO, JOURNAL, idPub, YEAR, GA, TYPE, " +
			"LANGUAGE, VOLUME, PAG, VOYPAG, PROVIENE, UTID, USUARIO";
	

	/**
	 * @see iedcyt.acute.dataAccess.idao.maauco.IAu0Dao#create()
	 */
	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from Ma0");
		} catch (Exception e) {
			String sqlins = "CREATE TABLE Ma0 (IDNO numeric, NUMAUTORES numeric, NUMORG numeric, TC numeric, RF numeric, TITULO varchar(255), JOURNAL varchar(255), idPub numeric, YEAR varchar(4), GA varchar(10), TYPE varchar(2), LANGUAGE varchar(2), VOLUME varchar(20), PAG varchar(20), VOYPAG varchar(20), PROVIENE varchar(30), UTID varchar(50), USUARIO varchar(50))";
			String sqlidx1 = "CREATE INDEX idxMa01 on Ma0 (idno) WITH PRIMARY DISALLOW NULL";
			String sqlidx2 = "CREATE INDEX idxMa02 on Ma0 (idPub)";
			String sqlidx3 = "CREATE INDEX idxMa03 on Ma0 (UTID)";
			_log.info(sqlins);
			_log.info(sqlidx1);
			_log.info(sqlidx2);
			_log.info(sqlidx3);
			_jdbcTemplate.execute(sqlins);
			_jdbcTemplate.execute(sqlidx1);
			_jdbcTemplate.execute(sqlidx2);
			_jdbcTemplate.execute(sqlidx3);
			
		}
	}
	
	@Override
	public List<Ma0> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM Ma0",
				new Ma0RowMapper());
	}
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from Ma0");
	}

	

	@Override
	public void insert(Ma0 doc) {
	
		_jdbcTemplate.update("INSERT INTO Ma0 (IDNO, NUMAUTORES, NUMORG, TC, RF, TITULO, JOURNAL, idPub, YEAR, GA, TYPE, LANGUAGE, VOLUME, PAG, VOYPAG, PROVIENE, UTID, USUARIO) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
				new Object[] { doc.get_IDNO(), doc.get_NUMAUTORES(), doc.get_NUMORG(),doc.get_TC(), doc.get_RF(), 
				(doc.get_TITULO()!=null)?doc.get_TITULO():"",
				(doc.get_JOURNAL()!=null)?doc.get_JOURNAL():"",
				doc.get_idPub(), 
				(doc.get_YEAR()!=null)?doc.get_YEAR():"",
						(doc.get_GA()!=null)?doc.get_GA():"",
						(doc.get_TYPE()!=null)?doc.get_TYPE():"", 
								(doc.get_LANGUAGE()!=null)?doc.get_LANGUAGE():"",
										(doc.get_VOLUME()!=null)?doc.get_VOLUME():"",
										(doc.get_PAG()!=null)?doc.get_PAG():"",
												(doc.get_VOYPAG()!=null)?doc.get_VOYPAG():"", 
														(doc.get_PROVIENE()!=null)?doc.get_PROVIENE():"",
																(doc.get_UTID()!=null)?doc.get_UTID():"", 
																		(doc.get_USUARIO()!=null)?doc.get_USUARIO():"" });
		
	}
	
	@Override
	public void update(Ma0 doc) {
	
		_jdbcTemplate.update("UPDATE Ma0 SET NUMAUTORES=?, NUMORG=?, TC = ? , RF = ?, TITULO = ?, JOURNAL = ?, idPub = ?, YEAR = ?, GA = ?, TYPE = ?, LANGUAGE = ?, VOLUME = ?, PAG = ?, VOYPAG = ?, PROVIENE = ?, UTID = ?, USUARIO = ? WHERE IDNO = ?",
				new Object[] { doc.get_NUMAUTORES(), doc.get_NUMORG(), doc.get_TC(), doc.get_RF(), doc.get_TITULO(), doc.get_JOURNAL(),
				doc.get_idPub(), doc.get_YEAR(), doc.get_GA(), doc.get_TYPE(), doc.get_LANGUAGE(), doc.get_VOLUME(),
				doc.get_PAG(), doc.get_VOYPAG(), doc.get_PROVIENE(), doc.get_UTID(), doc.get_USUARIO(), doc.get_IDNO() });
		
	}

	

	@Override
	public List<Ma0> selectByTitle(String title) {
		
		return _jdbcTemplate
				.query(
						"SELECT " + cols + " FROM Ma0 WHERE TITULO LIKE ?",
						new Object[] { title },
						new Ma0RowMapper());
	}
	
	@Override
	public void delete(int id) {
	
		_jdbcTemplate.update("DELETE from Ma0 WHERE idDoc=?",
				new Object[] { id  });
		
	}

	@Override
	public int selectMaxId() {
		return _jdbcTemplate.queryForInt("SELECT MAX(idno) FROM Ma0");
	}

	@Override
	public List<Ma0> selectByIdno(int idno) {
		return _jdbcTemplate
		.query(
				"SELECT " + cols + " FROM Ma0 WHERE IDNO = ?",
				new Object[] { idno },
				new Ma0RowMapper());
	}

	@Override
	public List<Ma0> selectByIdnoRange(int idno1, int idno2) {
		return _jdbcTemplate
		.query(
				"SELECT " + cols + " FROM Ma0 WHERE IDNO >= ? AND IDNO <= ?",
				new Object[] { idno1, idno2 },
				new Ma0RowMapper());
	}

	@Override
	public int selectMaxIdno() {
		return _jdbcTemplate.queryForInt("SELECT MAX(IDNO) FROM Ma0");
	}

	@Override
	public List<Ma0> selectByUTID(String uTID) {
		return _jdbcTemplate
		.query(
				"SELECT " + cols + " FROM Ma0 WHERE UTID LIKE ?",
				new Object[] { uTID },
				new Ma0RowMapper());
	}

	@Override
	public void updateIdPub(String UTID, int idPub) {
		_jdbcTemplate.update("UPDATE Ma0 SET idPub = ? WHERE UTID = ?",
				new Object[] { idPub, UTID });
	}

	
}

